/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package yuhuibear.poat.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 * @author yuhuibear
 */
public class DataBaseTool {

	private static Log log = LogFactory.getLog(DataBaseTool.class);

	public static boolean clearTable(DataSource source, String schema,
			String table) throws Exception {

		boolean done = false;
		Connection con = source.getConnection();
		try {

			Set<String> tables = listExistTables(con, schema, new String[] { "TABLE" });
			if (!tables.contains(table)) { return false; }
			PreparedStatement ps = con.prepareStatement("delete from " + table);
			if (ps == null) { return false; }
			done = ps.execute();
			log.info(" deleted all records of " + table);

		} catch (Exception ex) {
			con.rollback();
			log.error(DataBaseTool.class, ex);
		} finally {
			if (con != null) {
				con.close();
			}
		}
		return done;
	}

	public static Set<String> listExistTables(Connection con, String schema,
			String[] types) throws SQLException {
		Set<String> ts = new HashSet<String>();
		DatabaseMetaData dmd = con.getMetaData();

		ResultSet rs = dmd.getCatalogs();
		List<String> catalogs = new LinkedList<String>();
		while (rs.next()) {
			String catalog = (String) rs.getObject(1);
			catalogs.add(catalog);
		}
		for (String cat : catalogs) {
			rs = dmd.getTables(cat, schema, null, types);
			while (rs.next()) {
				String tablename = rs.getString("TABLE_NAME");
				ts.add(tablename);
			}
		}
		return ts;
	}

	public static Set<String> listExistFunctions(Connection con, String schema)
			throws SQLException {
		Set<String> ts = new HashSet<String>();
		DatabaseMetaData dmd = con.getMetaData();

		ResultSet rs = dmd.getCatalogs();
		List<String> catalogs = new LinkedList<String>();
		while (rs.next()) {
			String catalog = (String) rs.getObject(1);
			catalogs.add(catalog);
		}
		for (String cat : catalogs) {
			rs = dmd.getFunctions(cat, schema, "FUNCTION_NAME");
			while (rs.next()) {
				String tablename = rs.getString("TABLE_NAME");
				ts.add(tablename);
			}
		}
		return ts;
	}

	public static int clearSchema(DataSource source, String schema)
			throws Exception {
		int cnt = 0;
		Connection conDel = source.getConnection();
		try {

			Set<String> lst = listExistTables(conDel, schema, new String[] { "VIEW" });
			Set<String> done = new HashSet<String>();
			Map<String, String> tasks = new HashMap<String, String>();
			for (String view : lst) {
				tasks.put(view, "drop view " + view);
			}
			lst = listExistTables(conDel, schema, new String[] { "TABLE" });
			for (String table : lst) {
				tasks.put(table, "drop table " + table);
			}
			do {
				for (Map.Entry<String, String> entry : tasks.entrySet()) {
					try {
						conDel.createStatement().execute(entry.getValue());
						done.add(entry.getKey());
						log.info("delete  " + entry.getKey());
						cnt++;
					} catch (Exception e) {
						String msg = e.getMessage();
						if (msg.contains(entry.getKey())) {
							if (msg.contains("does not exist")) {
								done.add(entry.getKey());
							}
							continue;
						}
						else {
							conDel.rollback();
							throw e;
						}
					}
				}
				for (String iterm : done) {
					tasks.remove(iterm);
				}
				done.clear();
			} while (tasks.size() > 0);
			log.info("deleted " + cnt + " relations in total.");

			return cnt;
		} finally {
			conDel.close();
		}
	}

	public static ResultSet getResultSet(String sql, DataSource source)
			throws Exception {
		Connection con = source.getConnection();
		try {
			con = source.getConnection();
			Statement st = con.createStatement();
			return st.executeQuery(sql);
		} finally {
			con.close();
		}
	}
}
